﻿using Host.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Host.DB
{
    class DBReservations
    {
        DBConnection dbc = new DBConnection();
        DBBattery dbb = new DBBattery();
        DBLocation dbl = new DBLocation();
        DBUser dbu = new DBUser();


        public void addReservation(Reservation r)
        {
            string query = "INSERT INTO Reservations (userID, batteryID, timeStamp) VALUES (@userID, @batteryID, @timeStamp)";
            SqlConnection con = dbc.GetConnection();
            SqlCommand cmd = new SqlCommand(query, con);

            cmd.Parameters.AddWithValue("@userID", r.u.ID);
            cmd.Parameters.AddWithValue("@batteryID", r.b.ID);
            cmd.Parameters.AddWithValue("@timeStamp", r.timeStamp);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
        }

        public Reservation getReservationByID(string ID)
        {

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, userID, batteryID, timeStamp FROM Reservations WHERE ID = " + ID;
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            Reservation r = new Reservation();
            while (dr.Read())
            {
                r.ID = Convert.ToInt32(dr["ID"]);

                r.u = dbu.getUserByID(dr["userID"].ToString());
                r.b = dbb.getBattery(dr["batteryId"].ToString());
                r.timeStamp = dr["timeStamp"].ToString();

            }
            return r;

        }

        public List<Reservation> getReservationByUserID(User u)
        {

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, userID, batteryID, timeStamp FROM Reservations WHERE userID = " + u.ID;
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            List<Reservation> rl = new List<Reservation>();
            while (dr.Read())
            {
                Reservation r = new Reservation();
                r.ID = Convert.ToInt32(dr["ID"]);
                r.u = dbu.getUserByID(dr["userID"].ToString());
                r.b = dbb.getBattery(dr["batteryId"].ToString());
                r.timeStamp = dr["timeStamp"].ToString();

                rl.Add(r);

            }
            return rl;

        }

        public List<Reservation> getReservationByBatteryID(Battery b)
        {

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, userID, batteryID, timeStamp FROM Reservations WHERE userID = " + b.ID;
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            List<Reservation> rl = new List<Reservation>();
            while (dr.Read())
            {
                Reservation r = new Reservation();
                r.ID = Convert.ToInt32(dr["ID"]);
                r.u = dbu.getUserByID(dr["userID"].ToString());
                r.b = dbb.getBattery(dr["batteryId"].ToString());
                r.timeStamp = dr["timeStamp"].ToString();

                rl.Add(r);

            }
            return rl;

        }

        public List<Reservation> getAllReservation()
        {

            SqlConnection con = dbc.GetConnection();
            string query = "SELECT ID, userID, batteryID, timeStamp FROM Reservations";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader dr = cmd.ExecuteReader();
            List<Reservation> rl = new List<Reservation>();
            while (dr.Read())
            {
                Reservation r = new Reservation();
                r.ID = Convert.ToInt32(dr["ID"]);
                r.u = dbu.getUserByID(dr["userID"].ToString());
                r.b = dbb.getBattery(dr["batteryId"].ToString());
                r.timeStamp = dr["timeStamp"].ToString();

                rl.Add(r);

            }
            return rl;

        }

        public void deleteReservation(Reservation r)
        {
            SqlConnection con = dbc.GetConnection();
            string deleteSql = "DELETE FROM Reservations" + "WHERE ID=@ID";
            SqlCommand deleteCmd = new SqlCommand(deleteSql, con);
            deleteCmd.Parameters.AddWithValue("@ID", r.ID);

            try
            {
                deleteCmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: " + e.ToString());
            }
        }


    }
}
